using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class HollywoodLoactionService
    {
        public static bool AddHollywoodLoaction(HollywoodLoaction hollywoodLoaction)
        {
            string sql =
                "INSERT HollywoodLoaction (Location, IconID, HollywoodID)" +
                "VALUES (@Location, @IconID, @HollywoodID)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Location", hollywoodLoaction.Location),
				new SqlParameter("@IconID", hollywoodLoaction.IconID),
				new SqlParameter("@HollywoodID", hollywoodLoaction.HollywoodID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteHollywoodLoactionByID(int iD)
        {
            string sql = "DELETE HollywoodLoaction WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyHollywoodLoaction(HollywoodLoaction hollywoodLoaction)
        {
            string sql =
                "UPDATE HollywoodLoaction " +
                "SET " +
                    "Location = @Location, " +
                    "IconID = @IconID, " +
                    "HollywoodID = @HollywoodID " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", hollywoodLoaction.ID),
				new SqlParameter("@Location", hollywoodLoaction.Location),
				new SqlParameter("@IconID", hollywoodLoaction.IconID),
				new SqlParameter("@HollywoodID", hollywoodLoaction.HollywoodID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<HollywoodLoaction> GetAllHollywoodLoactions()
        {
            string sqlAll = "SELECT * FROM HollywoodLoaction";
            return GetHollywoodLoactionsBySql(sqlAll);
        }

        public static IList<HollywoodLoaction> GetHollywoodLoactionsByHollywoodID(int hollywoodID)
        {
            string sqlAll = "SELECT * FROM HollywoodLoaction  WHERE HollywoodID = @HollywoodID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@HollywoodID", hollywoodID)
			};
            return GetHollywoodLoactionsBySql(sqlAll, para);
        }

        public static HollywoodLoaction GetHollywoodLoactionByID(int iD)
        {
            string sql = "SELECT * FROM HollywoodLoaction WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            HollywoodLoaction hollywoodLoaction = new HollywoodLoaction();

            foreach (DataRow dr in temp.Rows)
            {
                hollywoodLoaction.ID = (int)dr["ID"];
                hollywoodLoaction.Location = (string)dr["Location"];
                hollywoodLoaction.IconID = (int)dr["IconID"];
                hollywoodLoaction.HollywoodID = (int)dr["HollywoodID"];

            }
            return hollywoodLoaction;

        }

        public static HollywoodLoaction GetHollywoodLoactionByIDAndH(int iD, int hollywoodID)
        {
            string sql = "SELECT * FROM HollywoodLoaction WHERE IconID = @IconID and HollywoodID=@HollywoodID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@IconID", iD),
                new SqlParameter("@HollywoodID", hollywoodID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            HollywoodLoaction hollywoodLoaction = new HollywoodLoaction();

            foreach (DataRow dr in temp.Rows)
            {
                hollywoodLoaction.ID = (int)dr["ID"];
                hollywoodLoaction.Location = (string)dr["Location"];
                hollywoodLoaction.IconID = (int)dr["IconID"];
                hollywoodLoaction.HollywoodID = (int)dr["HollywoodID"];

            }
            return hollywoodLoaction;

        }


        private static IList<HollywoodLoaction> GetHollywoodLoactionsBySql(string safeSql)
        {
            List<HollywoodLoaction> list = new List<HollywoodLoaction>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    HollywoodLoaction hollywoodLoaction = new HollywoodLoaction();

                    hollywoodLoaction.ID = (int)dr["ID"];
                    hollywoodLoaction.Location = (string)dr["Location"];
                    hollywoodLoaction.IconID = (int)dr["IconID"];
                    hollywoodLoaction.HollywoodID = (int)dr["HollywoodID"];

                    list.Add(hollywoodLoaction);
                }
            }
            return list;
        }

        private static IList<HollywoodLoaction> GetHollywoodLoactionsBySql(string safeSql, params SqlParameter[] para)
        {
            List<HollywoodLoaction> list = new List<HollywoodLoaction>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    HollywoodLoaction hollywoodLoaction = new HollywoodLoaction();

                    hollywoodLoaction.ID = (int)dr["ID"];
                    hollywoodLoaction.Location = (string)dr["Location"];
                    hollywoodLoaction.IconID = (int)dr["IconID"];
                    hollywoodLoaction.HollywoodID = (int)dr["HollywoodID"];

                    list.Add(hollywoodLoaction);
                }
            }
            return list;
        }
    }
}
